﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Npgsql;
using Ortodoctor.Componentes;
using System.Data;

namespace Ortodoctor.modelo
{
    public class modeloPromocao : conexao
    {
        NpgsqlCommand com;
     
        NpgsqlDataAdapter da;

        //conexao con = new conexao();
        public modeloPromocao()
        {
            conn = new NpgsqlConnection(connectionstring);
            conn.Open();
        }

        public void insert(Promocao c)
        {
            try
            {
                //con.open();
                String sql = "INSERT INTO promocao(imagem, texto, largura_imagem, altura_imagem, titulo, ativo, vencimento) VALUES (:p1, :p2, :p3, :p4, :p5, true, :p6)";
                com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", c.Img);
                com.Parameters.AddWithValue("p2", c.Texto);
                com.Parameters.AddWithValue("p3", c.Largura);
                com.Parameters.AddWithValue("p4", c.Altura);
                com.Parameters.AddWithValue("p5", c.Titulo);
                com.Parameters.AddWithValue("p6", Convert.ToDateTime(c.Date).ToString("MM/dd/yyyy"));
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { }
            finally
            {
                conn.Close();
            }
        }

        public DataTable verificaPromocaoExistente()
        {
            DataTable dt = new DataTable();
            try
            {
                
                String sql = "SELECT * FROM PROMOCAO ";
                da = new NpgsqlDataAdapter(sql, conn);
                try
                {
                    da.Fill(dt);
                }
                catch { }
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable verificaPromocaoExistenteValida()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM PROMOCAO WHERE VENCIMENTO >=  now()";
                da = new NpgsqlDataAdapter(sql, new NpgsqlConnection("Server=186.202.152.64;Port=5432;User Id=euamoaparelho1;Password=DycbqDfHLI;Database=euamoaparelho1;MinPoolSize=5;CommandTimeout=10000;"));
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        internal void delete(string titulo)
        {
            try
            {
                //con.open();
                String sql = "delete from promocao where titulo = :p1";
                com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", titulo);
                com.ExecuteNonQuery();

                sql = "update promocao_hist set id_usuario_modificacao=:id_mod, data_modificacao=:data where titulo = :p1";
                com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", titulo);
                com.Parameters.AddWithValue("id_mod", HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.Parameters.AddWithValue("data", DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss"));
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { }
            finally
            {
                conn.Close();
            }
        }

        internal void update(Promocao c)
        {
            try
            {
                //con.open();
                String sql = "update promocao set imagem=:p1, texto=:p2, largura_imagem=:p3, altura_imagem=:p4, id_usuario_modificacao=:id_mod, vencimento=:p6 where titulo = :p5";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", c.Img);
                com.Parameters.AddWithValue("p2", c.Texto);
                com.Parameters.AddWithValue("p3", c.Largura);
                com.Parameters.AddWithValue("p4", c.Altura);
                com.Parameters.AddWithValue("p5", c.Titulo);
                com.Parameters.AddWithValue("id_mod", HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.Parameters.AddWithValue("p6", Convert.ToDateTime(c.Date).ToString("MM/dd/yyyy"));
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { }
            finally
            {
                conn.Close();
            }
        }
    }
}